package dao

import "mysql/model"

// 易错点:
//	1.row -> Scan 否则不会释放连接
//	2.结果集要Close

// 不同数据库占位符 ? : $1 $2

func GetUserById(id int64) (*model.User, error) {
	sql := `select id, name, age from user where id = ?`
	user := new(model.User)
	err := client.QueryRow(sql, id).Scan(
		&user.Id, &user.Name, &user.Age)
	if err != nil {
		return nil, err
	}
	return user, nil
}

func GetUsers() ([]*model.User, error) {
	sql := `select id, name, age from user where id > ?`
	// 预编译处理
	stmt, err := client.Prepare(sql)
	if err != nil {
		return nil, err
	}
	defer func() { stmt.Close() }()

	rows, err := stmt.Query(0)
	if err != nil {
		return nil, err
	}
	defer func() { rows.Close() }()

	// stmt 预编译之后可以执行多次

	var users []*model.User
	for rows.Next() {
		user := new(model.User)
		err := rows.Scan(&user.Id, &user.Name, &user.Age)
		if err != nil {
			return nil, err
		}
		users = append(users, user)
	}
	return users, nil
	// 一般sql语句执行
	//var users []*model.User
	//rows, err := client.Query(sql, 0)
	//if err != nil {
	//	return nil, err
	//}
	//defer func() { rows.Close() }()
	//for rows.Next() {
	//	user := new(model.User)
	//	err := rows.Scan(&user.Id, &user.Name, &user.Age)
	//	if err != nil {
	//		return nil, err
	//	}
	//	users = append(users, user)
	//}
	//return users, nil
}

func InsertUser(user *model.User) (int64, error) {
	sql := `insert into user(name, age) values(?, ?)`
	result, err := client.Exec(sql, user.Name, user.Age)
	if err != nil {
		return 0, err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return 0, err
	}
	return id, nil
}

func UpdateUser(user *model.User) (int64, error) {
	sql := `update user set name = ?, age = ? where id = ?`
	result, err := client.Exec(sql, user.Name, user.Age, user.Id)
	if err != nil {
		return 0, err
	}
	affects, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return affects, nil
}

func DeleteUser(id int64) (int64, error) {
	sql := "delete from user where id = ?"
	result, err := client.Exec(sql, id)
	if err != nil {
		return 0, err
	}
	affects, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return affects, nil
}

// =============== 测试Sqlx 标准 api ===========

func GetUser(id int64) (*model.User, error) {
	var user model.User // 逃逸分析, user会自动分配到堆上
	err := clientSqlx.Get(
		&user,
		`select id, name, age from user where id = ?`,
		1)
	if err != nil {
		return nil, err
	}

	return &user, nil
}

func GetUsers2() ([]model.User, error) {
	var users []model.User
	err := clientSqlx.Select(
		&users,
		`select id, name, age from user  where id > ?`,
		0)
	if err != nil {
		return nil, err
	}

	return users, nil
}

func InsertUserByNamedExec() (int64, error) {
	// 命名参数查询, 根据命名参数, 查找对应属性进行查询
	// clientSqlx.NamedExec(sql, struct)
	result, err := clientSqlx.NamedExec(
		`insert into user (name, age) values (:name, :age);`,
		map[string]any{
			"name": "kite",
			"age":  30,
		})
	if err != nil {
		return 0, err
	}

	affected, err := result.RowsAffected()
	return affected, err
}
